From 13a0051c252969b2aa7ff82d99630648d58fb9ca Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 15 Jul 2026 15:52:26 +0000 Subject: [PATCH] Allow aggressons to have no time, the sdb_no_time value --- db/schemas/sokwedb/tables/create/events.m4 | 12 +++++- doc/src/epilog.inc.m4 | 3 ++ doc/src/tables/events.m4 | 43 +++++++++++++++++++++- include/limits.m4 | 4 ++ 4 files changed, 60 insertions(+), 2 deletions(-) diff --git a/db/schemas/sokwedb/tables/create/events.m4 b/db/schemas/sokwedb/tables/create/events.m4 index e0e2200..3142f9b 100644 --- a/db/schemas/sokwedb/tables/create/events.m4 +++ b/db/schemas/sokwedb/tables/create/events.m4 @@ -80,7 +80,9 @@ CREATE TABLE events ( ,start TIME NOT NULL noseconds_check(`Start') CONSTRAINT "Start cannot be before sdb_min_event_start" - CHECK (start >= 'sdb_min_event_start') + CHECK (start >= 'sdb_min_event_start' + OR (behavior = 'sdb_aggression' + AND start = 'sdb_no_time')) ,stop TIME NOT NULL noseconds_check(`Stop') CONSTRAINT "Stop cannot be after sdb_max_event_stop" @@ -93,6 +95,14 @@ CREATE TABLE events ( CONSTRAINT "Start cannot be after Stop" CHECK (start <= stop) + CONSTRAINT "Stop cannot be sdb_no_time unless Behavior is sdb_aggression" + CHECK (stop <> 'sdb_no_time' + OR behavior = 'sdb_aggression') + + CONSTRAINT "Start and Stop must either both be sdb_no_time or neither can be" + CHECK ((start = 'sdb_no_time' AND stop = 'sdb_no_time') + OR (start <> 'sdb_no_time' AND stop <> 'sdb_no_time')) + _point_behavior_time(`sdb_aggression') _point_behavior_time(`sdb_groom_scan') _point_behavior_time(`sdb_groom_scan_a') diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 45e3985..e245412 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -33,6 +33,9 @@ sdb_generated_rst()dnl .. _tri-valued logic: https://www.postgresql.org/docs/current/ functions-logical.html +.. _comparison functions: https://www.postgresql.org/docs/current/ + functions-comparison.html + .. _transaction isolation: https://www.postgresql.org/docs/current/transaction-iso.html diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index eb8df16..0dafa53 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -173,6 +173,10 @@ The following table lists these rules and implications: This means the value of the EVENTS.\ |EVENTS.Start| column must equal the value of the EVENTS.\ |EVENTS.Stop| column. + Aggressions are allowed to have no record of the time they occured, + in which case the special value of ``sdb_no_time`` is used. + See below for more detail. + For aggression events, the EVENTS.\ |EVENTS.Certainty| column records the certainty of the identity of the recipient, that is to say, the certainty of the individual with the role of @@ -661,6 +665,26 @@ The following table lists these rules and implications: For pantgrunt events, the EVENTS.\ |EVENTS.Certainty| column must be ``sdb_identity_certain``. +Aggressions may have no record of what time the aggression occurred. +In this case the special value of ``sdb_no_time``, midnight, is +recorded in the database.\ [#f2]_ +The following rules reflect this. + +The value of |EVENTS.Start| cannot be before ``sdb_min_event_start``. +The exception is that when the |EVENTS.Behavior| value is +``sdb_aggression``, an aggression, |EVENTS.Start| may have the +"special value" of ``sdb_no_time``. +This value indicates that no start time was recorded. + +When the |EVENTS.Behavior| value is ``sdb_aggression``, an aggression, +|EVENTS.Stop| may have the "special value" of ``sdb_no_time``. +This value indicates that no stop time was recorded. + +When no start time was recorded then no stop time may be recorded +either, and vice versa. +This means that when either |EVENTS.Start| or |EVENTS.Stop| is +``sdb_no_time``, then both |EVENTS.Start| and |EVENTS.Stop| must be +``sdb_no_time``. The |EVENTS.Start| time cannot be after the |EVENTS.Stop| time. @@ -771,7 +795,6 @@ That said, the precise semantics of this column depend on the :ref:`type of event ` being recorded -- the EVENTS.\ |EVENTS.Behavior| value. -The value of this column cannot be before ``sdb_min_event_start``. |noseconds| |notnull| @@ -835,3 +858,21 @@ Notes individual may have their location at a given time recorded using both methods. These two methods are not required to be in-sync. + +.. [#f2] + Because aggressions where there is no record of the time of + occurrence may have occurred at any time, the value of |null|, + which means "could be any value", would be a reasonable value to + use instead of midnight, ``sdb_no_time``. + + The implications of how |null|, meaning "any possible value", + affects comparison when querying, led to avoiding the |null| value + in this case. + The additional complexity |null| would introduce, both in the + queries users must write and in the system's internal code, have + not been thought through and so are best avoided at this time. + + For further information on what is required to work with |null| + values, see the PostgreSQL_ documentation's section on `tri-valued + logic`_, and the exensive discussion of the implications of the + |null| value in the `comparison functions`_ section. diff --git a/include/limits.m4 b/include/limits.m4 index f61a946..6a1ea96 100644 --- a/include/limits.m4 +++ b/include/limits.m4 @@ -73,6 +73,10 @@ dnl The latest possible time an event can occur in a follow. dnl (In 24 hour time) define(`sdb_max_event_stop', `20:00') +dnl The "special value" that aggressions are allowed to have +dnl that means that no time was recorded. +define(`sdb_no_time', `00:00') + dnl The earliest possible BIOGRAPHY_LOG.DateOfUpdate define(`sdb_first_biography_log_date', `2013-07-01') -- 2.34.1